﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Drawing;

namespace sistemaAcademicoFinanceiroEscolas
{
    public partial class uploadExcelProfessores : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            string connectionString = "";
            if (FileUpload1.HasFile)
            {
                string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
                string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
                string fileLocation = Server.MapPath("~/up/" + fileName);
                bool extensaoPermitida = false;
                bool arquivoGravado = false;

                if (FileUpload1.HasFile)
                {
                    string extensaoArquivo = Path.GetExtension(FileUpload1.FileName);

                    string[] extensoesPermitidas = { ".xls" }; 



                    for (int i = 0; i < extensoesPermitidas.Length; i++)
                        if (extensoesPermitidas[i] == extensaoArquivo)
                            extensaoPermitida = true;

                    if (extensaoPermitida == true)
                    {
                        try
                        {
                            FileUpload1.PostedFile.SaveAs(fileLocation);
                            arquivoGravado = true;
                        }
                        catch (Exception ex)
                        {
                            ErroLabel.ForeColor = Color.Red;
                            ErroLabel.Text = "Ocorreu um problema na tentativa de salvar o arquivo." + ex.ToString();
                            ErroLabel.Visible = true;
                            arquivoGravado = false;
                        }
                    }
                    else
                    {
                        ErroLabel.ForeColor = Color.Red;
                        ErroLabel.Text = string.Format("Tipo de arquivo inválido. Somente sao aceitos arquivos .xls.", extensoesPermitidas.ToString());
                        ErroLabel.Visible = true;
                    }

                    if (arquivoGravado)
                    {

                        ErroLabel.ForeColor = Color.Blue;
                        ErroLabel.Text = string.Format("Arquivo gravado com sucesso.");
                        ErroLabel.Visible = true;
                    }
                }                

                //Check whether file extension is xls or xslx

                if (fileExtension == ".xls")
                {
                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                }
                else if (fileExtension == ".xlsx")
                {
                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                }

                //Create OleDB Connection and OleDb Command

                OleDbConnection con = new OleDbConnection(connectionString);
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Connection = con;
                OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
                DataTable dtExcelRecords = new DataTable();
                con.Open();

                DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
                cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
                dAdapter.SelectCommand = cmd;
                dAdapter.Fill(dtExcelRecords);
                con.Close();

                //criação de strings para receber as linhas do excel
                string codigoProfessorExcel = "";
                string nomeProfessor = "";
                string telefoneProfessor = "";
                
                



                SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqlc.Open();
                SqlConnection sqld = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqld.Open();




                foreach (DataRow row in dtExcelRecords.Rows) // Loop over the rows.
                {
                    //Console.WriteLine("--- Row ---"); // Print separator.
                    //foreach (var item in row.ItemArray) // Loop over the items.
                    //{
                    //    Console.Write("Item: "); // Print label.
                    //    Console.WriteLine(item); // Invokes ToString abstract method.


                    //    // string recebendo o valor do excel e row é o numero da coluna.





                    //}

                    nomeProfessor = row[0].ToString();
                    telefoneProfessor = row[1].ToString();
                    



                    //1. fazer um select na tabela professor pesquisando se o nome do professor ja se encontra cadastrado  
                    string sqlVerificar = "select * from professor where nomeProfessor = '" + nomeProfessor + "'";

                    SqlCommand cmdProfessor = new SqlCommand(sqlVerificar, sqlc);
                    SqlDataReader myReaderVerificar = cmdProfessor.ExecuteReader();

                    

                    if (myReaderVerificar.HasRows)
                    {
                       
                        myReaderVerificar.Read();
                        //o professor existe na tabela, realizar update do professor

                        string sqlupdate = "update professor set nomeProfessor  = '" + nomeProfessor + "' , telefoneProfessor = '" + telefoneProfessor + "' where nomeProfessor = '" + nomeProfessor + "' and telefoneProfessor = '" + telefoneProfessor + "' ";

                        SqlCommand updateProfessor = new SqlCommand(sqlupdate, sqld);
                        updateProfessor.ExecuteNonQuery();

                    }
                    else
                    {

                        string inserir2 = "";

                        inserir2 += "INSERT INTO professor (nomeProfessor, telefoneProfessor)";                        
                        inserir2 += "VALUES ('"+ nomeProfessor +"', '" + telefoneProfessor + "')";
                        

                        SqlCommand inserirProfessor = new SqlCommand(inserir2, sqld);
                        inserirProfessor.ExecuteNonQuery();
                        //break;                



                    }

                    myReaderVerificar.Close();

                    //aqui

                    string literalc = "<script language=JavaScript>finalizar();</script>";
                    form1.Controls.Add(new LiteralControl(literalc));
                }

                sqlc.Close();
                sqld.Close();

            }
        }
    }
}